﻿Imports System.Threading.Tasks
Imports System.Threading

Module m_SyncProcess
    Public Sub SyncDo(response() As String, ConsoleSocket As Connections)
        'Master Tell us Database has Updated, Data Comes Here
        'Format: your-link-id*master_id*database*table_name~timestamp~exec_id~action~column_list~old_data~new_data#

        'Step 1: Split Link ID and Sync Data
        Dim data_1() As String
        Dim Msg As String = String.Empty
        Dim queryStat As Boolean = False

        'data_1()
        'Index 0: link_id
        'Index 1: master_id
        'Index 2: database
        data_1 = response(3).Split("*")

        If data_1.Length > 3 Then
            'Step 2: Get Sync Data Block
            Dim data_2() As String
            data_2 = data_1(3).Split("#")
            If data_2.Length >= 1 Then
                'Step 3: Get Sync Item
                Dim data_3() As String
                'Begin MySQL Transaction Here
                Dim db_tmp_table As New cMySQLDatabase
                Dim db_transaction As New cMySQLDatabase
                Dim field, old_data, new_data, last_timestamp, last_executionid As String
                Dim x As Long = 0
                Dim syncErr As Boolean = False
                Dim dbExists As Boolean = False, tmp_query As DataTable, tmp_obj As Object, dCount As Integer = 0, tmp_timestamp As String = "0000-00-00 00:00:00", tmp_executionid As String = "-1"

                db_tmp_table.OpenConnection()
                db_transaction.mysql_begin_transaction()

                'Set Database
                db_transaction.setTransactionDatabase(data_1(2))

                'Check Database Availability
                tmp_obj = db_tmp_table.continous_mysql_query("SHOW DATABASES WHERE `Database`='" & data_1(2) & "'")
                If Type.GetTypeCode(tmp_obj.GetType) <> TypeCode.Boolean Then
                    tmp_query = tmp_obj
                    If tmp_query.Rows.Count > 0 Then
                        db_tmp_table.setDatabase(data_1(2))
                        db_tmp_table.continous_mysql_non_query("DROP TEMPORARY TABLE IF EXISTS tmp_auditlog")
                        db_tmp_table.continous_mysql_non_query("CREATE TEMPORARY TABLE IF NOT EXISTS tmp_auditlog AS (SELECT * FROM auditlog_" & data_1(2) & ")")
                        tmp_obj = db_tmp_table.continous_mysql_query("SELECT execution_id,timestamp FROM tmp_auditlog ORDER BY timestamp ASC")
                        If Type.GetTypeCode(tmp_obj.GetType) <> TypeCode.Boolean Then
                            tmp_query = tmp_obj
                            dCount = tmp_query.Rows.Count
                            If dCount > 0 Then
                                tmp_timestamp = MinDate(tmp_query(dCount - 1)("timestamp"))
                                tmp_executionid = tmp_query(dCount - 1)("execution_id")
                            End If
                            dbExists = True
                        Else
                            dbExists = False
                        End If
                    End If
                End If
                '------------------------------------------------
                
                last_timestamp = ""
                last_executionid = ""
                For i As Integer = 0 To data_2.Length - 1
                    'data_2()
                    'Index 0: table_name
                    'Index 1: timestamp
                    'Index 2: exec_id
                    'Index 3: action
                    'Index 4: PK
                    'Index 5: column_list
                    'Index 6: old_data
                    'Index 7: new_data
                    data_3 = data_2(i).Split("~")
                    field = ""
                    old_data = ""
                    new_data = ""
                    If i = data_2.Length - 1 Then
                        last_timestamp = data_3(1)
                        last_executionid = data_3(2)
                    End If

                    'Now we get the data per index, create query string
                    If data_3(3).Equals("I") Then 'For Insert Process
                        'COLUMN LIST
                        For Each k As String In data_3(5).Split(",")
                            field = field & "`" & k & "`,"
                        Next
                        If field.Substring(field.Length - 1).Equals(",") Then field = field.Substring(0, field.Length - 1)
                        'VALUES LIST
                        If data_3(7).Equals("") = False Then
                            For Each k As String In data_3(7).Split(",")
                                If k.Equals("(NULL)") Then
                                    k = "NULL"
                                Else
                                    k = "'" & k & "'"
                                End If

                                new_data = new_data & k & ","
                            Next
                            If new_data.Substring(new_data.Length - 1).Equals(",") Then new_data = new_data.Substring(0, new_data.Length - 1)
                        End If
                        queryStat = db_transaction.mysql_transaction_non_query(String.Format("INSERT INTO {0}({1}) VALUES ({2})", data_3(0), field, new_data))
                        If queryStat = False Then syncErr = True
                    ElseIf data_3(3).Equals("U") Then 'For Update Process
                        'CREATE SET
                        x = 0
                        For Each k As String In data_3(5).Split(",")
                            If data_3(7).Split(",")(x).ToString.Equals("(NULL)") Then
                                field = field & k & "=NULL,"
                            Else
                                field = field & k & "='" & data_3(7).Split(",")(x) & "',"
                            End If

                            x = x + 1
                        Next
                        If field.Substring(field.Length - 1).Equals(",") Then field = field.Substring(0, field.Length - 1)
                        'CREATE WHERE
                        x = 0
                        For Each k As String In data_3(5).Split(",")
                            If data_3(6).Split(",")(x).ToString.Equals("(NULL)") Then
                                new_data = new_data & k & " IS NULL AND "
                            Else
                                new_data = new_data & k & "='" & data_3(6).Split(",")(x) & "' AND "
                            End If

                            x = x + 1
                        Next
                        If new_data.IndexOf("AND") Then new_data = new_data.Substring(0, new_data.Length - 5)

                        queryStat = db_transaction.mysql_transaction_non_query(String.Format("UPDATE {0} SET {1} WHERE {2}", data_3(0), field, new_data))
                        If queryStat = False Then syncErr = True
                    ElseIf data_3(3).Equals("D") Then 'For Delete Process
                        'CREATE WHERE
                        x = 0
                        For Each k As String In data_3(5).Split(",")
                            If data_3(6).Split(",")(x).ToString.Equals("(NULL)") Then
                                old_data = old_data & k & " IS NULL AND "
                            Else
                                old_data = old_data & k & "='" & data_3(6).Split(",")(x) & "' AND "
                            End If

                            x = x + 1
                        Next
                        If old_data.IndexOf("AND") Then old_data = old_data.Substring(0, old_data.Length - 5)
                        queryStat = db_transaction.mysql_transaction_non_query(String.Format("DELETE FROM {0} WHERE {1}", data_3(0), old_data))
                        If queryStat = False Then syncErr = True
                    End If
                Next

                'Recheck if new data available in auditlog
                If dbExists Then
                    tmp_obj = db_tmp_table.continous_mysql_query("SELECT execution_id,timestamp FROM auditlog_" & data_1(2) & "")
                    If Type.GetTypeCode(tmp_obj.GetType) <> TypeCode.Boolean Then
                        tmp_query = tmp_obj
                        If tmp_query.Rows.Count > dCount Then
                            If dCount > 0 Then
                                db_tmp_table.continous_mysql_non_query(String.Format("INSERT INTO tmp_auditlog SELECT * FROM auditlog_" & data_1(2) & " WHERE execution_id>{0} AND timestamp>={1}", tmp_executionid, tmp_timestamp))
                            Else
                                db_tmp_table.continous_mysql_non_query("INSERT INTO tmp_auditlog SELECT * FROM auditlog_" & data_1(2))
                            End If
                        End If
                    End If
                End If

                If syncErr = False Then
                    db_transaction.mysql_commit_transaction()
                    Msg = String.Format("/dbsyncdoresponse{0}{1}{0}{2}{0}{3}{0}{4}", Packet.pSplit, "0", data_1(0), last_timestamp, last_executionid)

                    'RESTORE tmp_auditlog
                    If dbExists Then
                        db_tmp_table.continous_mysql_non_query(String.Format("TRUNCATE TABLE {0}", "auditlog_" & data_1(2)))
                        db_tmp_table.continous_mysql_non_query(String.Format("INSERT INTO {0} SELECT * FROM tmp_auditlog", "auditlog_" & data_1(2)))
                    End If

                    Try
                        ConsoleSocket.sendMessages(Msg, response(1))
                    Catch ex As Exception
                        db_transaction.mysql_non_query(String.Format("INSERT INTO tb_msg_outbox(`date_sent`,`message`,`user_target`,`status`) VALUES ('{0}','{1}','{2}','0')", Date.Now.ToString("yyyy-MM-dd HH:mm:ss"), Msg, data_1(1)))
                    End Try
                Else
                    'ASD

                End If

                'End Transaction
                db_tmp_table.CloseConnection()
                db_transaction.mysql_end_transaction()
            End If
        End If

    End Sub

    Public Sub SyncDoResponse(response() As String, ConsoleSocket As Connections)
        If response.Length >= 3 Then
            Dim mysql As New cMySQLDatabase
            Select Case response(3)
                Case "0"
                    mysql.mysql_non_query("UPDATE tb_syncconfig_link SET last_timestamp='" & response(5) & "',last_executionid='" & response(6) & "',synchronized='0' WHERE id='" & response(4) & "'")
                Case Else

            End Select
        End If
    End Sub
End Module
